// AdminWrongQuestionMapper.java
package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.AdminWrongQuestionDetailVO;
import com.scuec.groupfive.vo.AdminWrongQuestionOverviewVO;
import org.apache.ibatis.annotations.*;
import java.util.Date;
import java.util.List;

@Mapper
public interface AdminWrongQuestionMapper {

    @Select("<script>" +
            "SELECT " +
            "  wq.question_id AS wrongId, " +
            "  s.subject_name AS subjectName, " +
            "  c.chapter_name AS chapterName, " +
            "  GROUP_CONCAT(DISTINCT kp.point_name) AS pointNames, " +
            "  et.exam_type AS examTypeName, " +
            "  CONCAT(LEFT(q.content, 50), '...') AS content, " +
            "  wq.wrong_count AS wrongCount, " +
            "  wq.last_wrong_time AS lastWrongTime " +
            "FROM wrong_questions wq " +
            "JOIN questions q ON wq.question_id = q.question_id " +
            "JOIN subjects s ON q.subject_id = s.subject_id " +
            "JOIN exam_types et ON q.type_id = et.type_id " +
            "LEFT JOIN chapters c ON q.chapter_id = c.chapter_id " +
            "LEFT JOIN question_knowledge qk ON q.question_id = qk.question_id " +
            "LEFT JOIN knowledge_points kp ON qk.point_id = kp.point_id " +
            "WHERE wq.user_id = #{userId} " +
            "AND q.question_type IN ('single_choice', 'fill_in_the_blank') " +
            "<if test='subjectId != null'> " +
            "  AND q.subject_id = #{subjectId} " +
            "</if> " +
            "<if test='startDate != null'> " +
            "  AND wq.last_wrong_time &gt;= #{startDate, jdbcType=TIMESTAMP} " +
            "</if> " +
            "<if test='endDate != null'> " +
            "  AND wq.last_wrong_time &lt;= #{endDate, jdbcType=TIMESTAMP} " +
            "</if> " +
            "GROUP BY wq.question_id " +
            "<choose>" +
            "  <when test='sort == \"time\"'>" +
            "    ORDER BY wq.last_wrong_time DESC" +
            "  </when>" +
            "  <when test='sort == \"errorCount\"'>" +
            "    ORDER BY wq.wrong_count DESC" +
            "  </when>" +
            "  <when test='sort == \"chapter\"'>" +
            "    ORDER BY " +
            "      CASE " +
            "        WHEN s.subject_name = '高等数学' THEN 1 " +
            "        WHEN s.subject_name = '线性代数' THEN 2 " +
            "        WHEN s.subject_name = '概率论与数理统计' THEN 3 " +
            "        ELSE 4 " +
            "      END, " +
            "      CAST(REPLACE(c.chapter_name, '第', '') AS SIGNED) ASC" +
            "  </when>" +
            "  <otherwise>" +
            "    ORDER BY wq.last_wrong_time DESC" +
            "  </otherwise>" +
            "</choose>" +
            "</script>")
    List<AdminWrongQuestionOverviewVO> getWrongQuestionOverview(
            @Param("userId") Long userId,
            @Param("subjectId") Integer subjectId,
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate,
            @Param("sort") String sort);

    @Select("<script>" +
            "SELECT " +
            "  wq.id AS wrongId, " +
            "  wq.question_id AS questionId, " +
            "  s.subject_name AS subjectName, " +
            "  c.chapter_name AS chapterName, " +
            "  GROUP_CONCAT(DISTINCT kp.point_name) AS pointNames, " +
            "  et.exam_type AS examTypeName, " +
            "  q.content AS content, " +
            "  wq.wrong_count AS wrongCount, " +
            "  wq.last_wrong_time AS lastWrongTime, " +
            "  wq.is_favorite AS isFavorite, " +
            "  (SELECT question_type FROM answer_records " +
            "   WHERE user_id = #{userId} AND question_id = wq.question_id " +
            "   ORDER BY answered_at DESC LIMIT 1) AS questionType, " +
            "  (SELECT answer FROM answer_records " +
            "   WHERE user_id = #{userId} AND question_id = wq.question_id " +
            "   ORDER BY answered_at DESC LIMIT 1) AS userAnswer, " +
            "  CASE " +
            "    WHEN q.question_type = 'single_choice' THEN cq.answer " +
            "    WHEN q.question_type = 'fill_in_the_blank' THEN ( " +
            "      SELECT GROUP_CONCAT(JSON_UNQUOTE(JSON_EXTRACT(blanks, '$[*].answer'))) " +
            "      FROM fill_blank_questions " +
            "      WHERE question_id = q.question_id " +
            "    ) " +
            "  END AS correctAnswer, " +
            "  CASE " +
            "    WHEN q.question_type = 'single_choice' THEN cq.analysis " +
            "    WHEN q.question_type = 'fill_in_the_blank' THEN fq.analysis " +
            "  END AS questionAnalysis, " +
            "  nr.note_text AS noteContent, " +
            "  CASE " +
            "    WHEN q.question_type = 'single_choice' THEN cq.options " +  // 新增options查询
            "    ELSE NULL " +
            "  END AS options " +
            "FROM wrong_questions wq " +
            "JOIN questions q ON wq.question_id = q.question_id " +
            "JOIN subjects s ON q.subject_id = s.subject_id " +
            "LEFT JOIN exam_types et ON q.type_id = et.type_id " +
            "LEFT JOIN chapters c ON q.chapter_id = c.chapter_id " +
            "LEFT JOIN question_knowledge qk ON q.question_id = qk.question_id " +
            "LEFT JOIN knowledge_points kp ON qk.point_id = kp.point_id " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id " +
            "  AND q.question_type = 'single_choice' " +
            "LEFT JOIN fill_blank_questions fq ON q.question_id = fq.question_id " +
            "  AND q.question_type = 'fill_in_the_blank' " +
            "LEFT JOIN note_records nr ON wq.user_id = nr.user_id " +
            "  AND wq.question_id = nr.question_id " +
            "WHERE wq.user_id = #{userId} " +
            "  AND wq.question_id = #{questionId} " +
            "  AND q.question_type IN ('single_choice', 'fill_in_the_blank') " +
            "GROUP BY wq.id, wq.question_id, s.subject_name, c.chapter_name, et.exam_type, " +
            "         q.content, wq.wrong_count, wq.last_wrong_time, wq.is_favorite, q.question_type, " +
            "         cq.answer, cq.analysis, fq.analysis, nr.note_text, cq.options " +  // 添加cq.options到GROUP BY
            "</script>")
    AdminWrongQuestionDetailVO getWrongQuestionDetail(
            @Param("userId") Long userId,
            @Param("questionId") String questionId);

    @Select("<script>" +
            "SELECT COUNT(DISTINCT wq.question_id) " +
            "FROM wrong_questions wq " +
            "JOIN questions q ON wq.question_id = q.question_id " +
            "WHERE wq.user_id = #{userId} " +
            "AND q.question_type IN ('single_choice', 'fill_in_the_blank') " +  // 只筛选选择题和填空题
            "<if test='subjectId != null'> " +
            "  AND q.subject_id = #{subjectId} " +
            "</if> " +
            "<if test='startDate != null'> " +
            "  AND wq.last_wrong_time &gt;= #{startDate, jdbcType=TIMESTAMP} " +
            "</if> " +
            "<if test='endDate != null'> " +
            "  AND wq.last_wrong_time &lt;= #{endDate, jdbcType=TIMESTAMP} " +
            "</if> " +
            "</script>")
    long countWrongQuestions(
            @Param("userId") Long userId,
            @Param("subjectId") Integer subjectId,
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate);
}